# -*- coding: utf-8 -*-
from django.shortcuts import render
import MySQLdb
from rest_framework.permissions import AllowAny
from rest_framework.views import APIView
from rest_framework.status import (
    HTTP_400_BAD_REQUEST,
    HTTP_201_CREATED,
    HTTP_200_OK,
    HTTP_404_NOT_FOUND,
    HTTP_403_FORBIDDEN)
from rest_framework.response import Response
from django.db import connection
from django.core.cache import cache
import time
import json


class PersonInYear(APIView):
    """
    根据年份返回作者和手稿总数
    """
    permission_classes = [AllowAny]

    def get(self,request):
        year = request.GET.get("year")
        if year:
            print year
            reply = list()
            try:
                conn = MySQLdb.Connect(
                    host='127.0.0.1',
                    port=3306,
                    user='root',
                    passwd='1234',
                    db='sh11',
                    charset='utf8'
                )
                cursor = conn.cursor()
                sql = "select a.fname,count(a.fname),creator_uri from (select * from work where real_year="\
                      + str(year) + ") as a group by a.fname  order by count(a.fname) desc"
                cursor.execute(sql)
                rs = cursor.fetchall()
                print len(rs)
                for row in rs:
                    reply_dict = dict()
                    reply_dict['fname'] = row[0]
                    reply_dict['count'] = row[1]
                    reply_dict['creator_uri'] = row[2]
                    reply.append(reply_dict)
                return Response(reply, HTTP_200_OK)
            except Exception as e:
                print e
                return Response(HTTP_404_NOT_FOUND)
        else:
            return Response(HTTP_404_NOT_FOUND)


class PersonYearWork(APIView):
    """
    根据作者,年份返回相应的手稿
    """
    permission_classes = [AllowAny]

    def get(self,request):
        year = request.GET.get("year")
        uri = request.GET.get("uri")
        reply = list()
        try:
            conn = MySQLdb.Connect(
                host='127.0.0.1',
                port=3306,
                user='root',
                passwd='1234',
                db='sh11',
                charset='utf8'
            )
            cursor = conn.cursor()
            sql = "select title,uri,typeValue from work where " + \
                " real_year = '%s' and creator_uri = '%s'" % (str(year),str(uri))
            cursor.execute(sql)
            rs = cursor.fetchall()
            for row in rs:
                reply_dict = dict()
                reply_dict['title'] = row[0]
                reply_dict['worK_uri'] = row[1]
                reply_dict['typeValue'] = row[2]
                reply.append(reply_dict)
            return Response(reply,HTTP_200_OK)
        except Exception as e:
            print e
            return Response(HTTP_404_NOT_FOUND)


class RelationSum(APIView):
    """
    大图,谁和谁有关系,关系量
    """
    pass


def get_friends(uri):
    import requests
    person_request = requests.get(uri+".json")
    response = json.loads(person_request.text.encode("utf-8"))['result']['data']
    reply = list()
    if response.has_key("friendOf"):
        conn = MySQLdb.Connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='1234',
            db='sh11',
            charset='utf8'
        )

        try:
            friend_list = response['friendOf']
            for i in friend_list:
                cursor = conn.cursor()
                sql = "select fname from person where uri = '%s'" % i
                cursor.execute(sql)
                rs = cursor.fetchall()
                for row in rs:
                    reply_dict = dict()
                    reply_dict['name'] = row[0]
                    reply_dict['person_uri'] = i
                    reply.append(reply_dict)
        except:
            pass
    return reply


class PersonInfo(APIView):
    """
    得到人物的基本信息
    """
    permission_classes = [AllowAny]

    def get(self,request):
        conn = MySQLdb.Connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='1234',
            db='sh11',
            charset='utf8'
        )
        cursor = conn.cursor()
        if request.GET.get("uri"):
            person_uri = request.GET.get("uri")
            friends = get_friends(person_uri)
            sql = "select * from person where uri = '%s'" % person_uri
            cursor.execute(sql)
            rs = cursor.fetchall()
            for row in rs:
                reply = dict()
                reply['uri'] = row[0]
                reply['name'] = row[1]
                reply['start'] = row[2]
                reply['end'] = row[3]
                reply['place_uri'] = row[4]
                reply['place'] = row[5]
                reply['lon'] = row[6]
                reply['lat'] = row[7]
                reply['speciality'] = row[8]
                reply['brirfBio'] = row[9]
                reply['friends'] = friends
                conn.close()
                return Response(reply,HTTP_200_OK)
        elif request.GET.get("name"):
            fname = request.GET.get("name")
            sql = "select * from person where fname = '%s'" % fname
            try:
                cursor.execute(sql)
                rs = cursor.fetchall()
                for row in rs:
                    reply = dict()
                    reply['uri'] = row[0]
                    reply['name'] = row[1]
                    reply['start'] = row[2]
                    reply['end'] = row[3]
                    reply['place_uri'] = row[4]
                    reply['place'] = row[5]
                    reply['lon'] = row[6]
                    reply['lat'] = row[7]
                    reply['speciality'] = row[8]
                    reply['brirfBio'] = row[9]
                    friends = get_friends(row[0])
                    reply['friends'] = friends
                    conn.close()
                    return Response(reply, HTTP_200_OK)
            except:
                return Response(HTTP_404_NOT_FOUND)
        else:
            conn.close()
            return Response(HTTP_404_NOT_FOUND)


class ShouGaoInfo(APIView):
    """
    得到手稿的基本信息
    """
    permission_classes = [AllowAny]

    def get(self,request):
        conn = MySQLdb.Connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='1234',
            db='sh11',
            charset='utf8'
        )
        cursor = conn.cursor()
        if request.GET.get("uri"):
            work_uri = request.GET.get("uri")
            sql = "select * from work where uri='%s'" % work_uri
            try:
                cursor.execute(sql)
                rs = cursor.fetchall()
                for row in rs:
                    reply = dict()
                    reply['basic_info'] = dict()
                    reply['basic_info']['uri'] = row[0]
                    reply['basic_info']['title'] = row[1]
                    reply['basic_info']['typeValue'] = row[2]
                    reply['basic_info']['creator'] = row[3]
                    reply['basic_info']['creator_uri'] = row[4]
                    reply['basic_info']['owner'] = row[5]
                    reply['basic_info']['owner_uri'] = row[6]
                    reply['basic_info']['year'] = row[7]
                    reply['basic_info']['place_uri'] = row[8]
                    return Response(reply,HTTP_200_OK)
            except:
                return Response(HTTP_404_NOT_FOUND)
        else:
            return Response(HTTP_404_NOT_FOUND)


class PersonShouGao(APIView):
    """
    人物页:返回一个人所有的手稿
    """
    def get(self,request):
        if request.GET.get("uri"):
            uri = request.GET.get("uri")
            reply = list()
            try:
                conn = MySQLdb.Connect(
                    host='127.0.0.1',
                    port=3306,
                    user='root',
                    passwd='1234',
                    db='sh11',
                    charset='utf8'
                )
                cursor = conn.cursor()
                sql = "select title,uri,typeValue from work where " + \
                    "creator_uri = '%s'" % (str(uri))
                cursor.execute(sql)
                rs = cursor.fetchall()
                count = len(rs)
                reply.append({'count': count})
                for row in rs:
                    reply_dict = dict()
                    reply_dict['title'] = row[0]
                    reply_dict['worK_uri'] = row[1]
                    reply_dict['typeValue'] = row[2]
                    reply.append(reply_dict)
                return Response(reply,HTTP_200_OK)
            except Exception as e:
                print e
                return Response(HTTP_404_NOT_FOUND)
        elif request.GET.get("name"):
            fname = request.GET.get("name")
            reply = list()
            try:
                conn = MySQLdb.Connect(
                    host='127.0.0.1',
                    port=3306,
                    user='root',
                    passwd='1234',
                    db='sh11',
                    charset='utf8'
                )
                cursor = conn.cursor()
                sql = "select title,uri,typeValue from work where " + \
                      "fname = '%s'" % (fname)
                cursor.execute(sql)
                rs = cursor.fetchall()
                count = len(rs)
                reply.append({'count': count})
                for row in rs:
                    reply_dict = dict()
                    reply_dict['title'] = row[0]
                    reply_dict['worK_uri'] = row[1]
                    reply_dict['typeValue'] = row[2]
                    reply.append(reply_dict)
                return Response(reply, HTTP_200_OK)
            except Exception as e:
                print e
                return Response(HTTP_404_NOT_FOUND)

        else:
            return Response(HTTP_404_NOT_FOUND)


class BirthPlaceCount(APIView):
    """获取单个地点出生的人物"""
    permission_classes = [AllowAny]

    def get(self,request):
        reply = dict()
        try:
            conn = MySQLdb.Connect(
                host='127.0.0.1',
                port=3306,
                user='root',
                passwd='1234',
                db='sh11',
                charset='utf8'
            )
            cursor = conn.cursor()
            sql = "select place_province,count(place_province) from person group by place_province"
            cursor.execute(sql)
            rs = cursor.fetchall()
            for row in rs:
                reply[row[0]] = row[1]
            return Response(reply,HTTP_200_OK)
        except:
            return Response(HTTP_400_BAD_REQUEST)


class PlaceDetail(APIView):
    """ 获取某个地方具体有哪些人 """

    permission_classes = [AllowAny]

    def get(self,request):
        reply = list()
        p = request.GET.get("p")
        try:
            conn = MySQLdb.Connect(
                host='127.0.0.1',
                port=3306,
                user='root',
                passwd='1234',
                db='sh11',
                charset='utf8'
            )
            cursor = conn.cursor()
            sql = "select fname,uri,place from person where place_province = '%s'" % p
            cursor.execute(sql)
            rs = cursor.fetchall()
            reply.append({'count':len(rs)})
            for row in rs:
                reply_dict = dict()
                reply_dict['name'] =  row[0]
                reply_dict['uri'] = row[1]
                reply_dict['place'] = row[2]
                reply.append(reply_dict)
            return Response(reply,HTTP_200_OK)
        except:
            return Response(HTTP_404_NOT_FOUND)


class AllEvent(APIView):
    """ 得到所有事件 """

    def get(self,request):
        with open('event/event.json') as json_file:
            data = json.load(json_file)
        if request.GET.get("key"):
            name = request.GET.get("key")
            for event in data:
                if event['keyword'] == name:
                    return Response(event,HTTP_200_OK)
            return Response(HTTP_404_NOT_FOUND)
        else:
            return Response(data,HTTP_200_OK)


class ReadJson(APIView):
    """ 读取json文件"""

    permission_classes = [AllowAny]

    def get(self,request):
        with open('event/china.json') as json_file:
            data = json.load(json_file)
        return Response(data,HTTP_200_OK)


class RelationByYear(APIView):
    """ 得到某一年谁和谁有关系  """
    permission_classes = [AllowAny]

    def get(self,request):
        if request.GET.get("year"):
            year = request.GET.get("year")
            try:
                conn = MySQLdb.Connect(
                    host='127.0.0.1',
                    port=3306,
                    user='root',
                    passwd='1234',
                    db='sh11',
                    charset='utf8'
                )
                cursor = conn.cursor()
                sql = "select relation_key,count(*) from hold_relation \
                where year= '%s' group by relation_key order by count(*) desc;" % year
                cursor.execute(sql)
                rs = cursor.fetchall()
                reply = list()
                for row in rs:
                    person_list =  row[0].split("&")
                    if len(person_list) == 2:
                        if person_list[0] == person_list[1]:
                            continue
                    reply_dict = dict()
                    reply_dict['key'] = row[0].replace("&","to")
                    reply_dict['count'] = row[1]
                    reply.append(reply_dict)
                return Response(reply, HTTP_200_OK)
            except:
                return Response(HTTP_404_NOT_FOUND)


class GetRelationDetail(APIView):
    permission_classes = [AllowAny]

    def get(self,request):
        key = request.GET.get("key").replace(u"to","&")
        year =  request.GET.get("year")
        try:
            conn = MySQLdb.Connect(
                host='127.0.0.1',
                port=3306,
                user='root',
                passwd='1234',
                db='sh11',
                charset='utf8'
            )
            cursor = conn.cursor()
            sql = "select w.title,r.work_uri,w.year from \
            work as w,hold_relation as r where r.year= '%s' \
            and r.relation_key = '%s' and w.uri=r.work_uri;" % (year,key)
            cursor.execute(sql)
            rs = cursor.fetchall()
            reply = list()
            for row in rs:
                reply_dict = dict()
                reply_dict['title'] = row[0]
                reply_dict['worK_uri'] = row[1]
                reply_dict['date'] = row[2]
                reply.append(reply_dict)
            return Response(reply, HTTP_200_OK)
        except Exception as e:
            print e
            return Response(HTTP_404_NOT_FOUND)


def index(request):
    return render(request, 'index.html')